﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMSX_get_deploy_list]
#-- Purpose:		Gets a list of servers that have not had centraldba deployed
#--					to them yet.
#--	Last Update:	04/06/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMSX_get_deploy_list]
(
	@has_centraldba		bit		= NULL,
	@include_test		bit		= 1,
	@include_qa			bit		= 0,
	@include_prod		bit		= 0
)
AS

SET NOCOUNT ON

SELECT [ssd_type_ro],
	[ssd_sqlserver_name_ro],
	[ssd_server_name],
	[sql_has_centraldba_db],
	[sql_backup_server_ro],
	[ssd_sql_service],
	[ssd_agent_service],
	[sql_has_master_jobs],
	[ssd_instance_name],
	ssd.[ssd_deploy_msx_backup],
	ssd.[ssd_deploy_msx_index],
	ssd.[ssd_is_mission_critical],
	ssql.[sql_version_major_ro],
	ssd.[ssd_cluster_name]
FROM [dbo].[tblMSX_server_sql_info] ssql
INNER JOIN [dbo].[tblMSX_server_discovery] ssd ON ssql.[sql_ssd_id] = ssd.[ssd_id]
WHERE ssd.[ssd_is_vendor_managed_ro] = 0							--- Don't deploy to vendor managed
	AND ssd.[ssd_is_disabled] = 0									--- Don't deploy to disabled servers
	AND ssql.[sql_version_major_ro] > 8								--- Don't deploy to SQL Server 2000
	AND (
		@has_centraldba IS NULL										--- Regardless of whether CentralDBA has been deployed (keep it matching)
		OR [sql_has_centraldba_db] = @has_centraldba				--- 0 = Only if CentralDBA is not deployed; 1 = CentralDBA has been deployed
		OR (
			LTRIM(RTRIM(ISNULL([sql_backup_server_ro], ''))) = ''	--- Does not have a UNC backup path yet (set backup path)
			AND [ssd_sql_service] NOT IN (							--- UNC backups can't be used for LocalSystem/Service
				'LocalSystem', 
				'LocalService'
			)
		)
	)
	AND (
		(@include_prod = 1 AND [ssd_type_ro] = 'Production')		--- Include production servers?
		OR (@include_qa = 1 AND [ssd_type_ro] = 'QA')				--- Include QA servers?
		OR (@include_test = 1 AND [ssd_type_ro] = 'Test')			--- Include test servers?
	)
ORDER BY (
		CASE [ssd_type_ro]
			WHEN 'Test'
				THEN 1
			WHEN 'QA'
				THEN 2
			ELSE 3
			END
		),
	[sql_version_major_ro],
	[sql_version_minor_ro],
	[ssd_server_name],
	[ssd_sqlserver_name_ro]

SET NOCOUNT OFF
